# coding=utf-8
from framework.db.mysql import BaseDb
from tornado import gen
from lib import log


class Device(BaseDb):

    @gen.coroutine
    def select_device_type(self, Fschool_id):
        sql = 'SELECT Fid, Fname, Fschool_id, Fcreate_time FROM tb_material_type WHERE Fschool_id = %s'

        ret = yield self.executejson(sql, (Fschool_id))

        raise gen.Return(ret)

    @gen.coroutine
    def insert_device(self, device_info):

        ret = yield self.insert("tb_material", device_info)

        self.commit()

        raise gen.Return(ret)

    @gen.coroutine
    def select_device(self, school_id, where, page, offset):
        sql = '''SELECT a.Fid, a.Fname, a.Ftype_id, b.Fdorm_name, c.Fname Froom 
                , a.Ffloor, a.Fimg, a.Fqrcode, a.Fqrcode, a.Fcreate_time, a.Fcode
                FROM tb_material a 
                LEFT JOIN tb_dorm b 
                ON a.Fdorm_id = b.Fid
                LEFT JOIN tb_room c 
                ON a.Froom_id = c.Fid
                #LEFT JOIN tb_material_type d 
                #ON a.Ftype_id = d.Fid
                WHERE a.Fschool_id = %s %s 
                ORDER BY a.Fcreate_time DESC
                LIMIT %s, %s'''
        count_sql = 'SELECT count(a.Fid) FROM tb_material a WHERE a.Fschool_id = %s %s'

        wheres = ''
        for i in where.keys():
            if where[i] != '':
                wheres += " AND a." + i + "='" + where[i] + "'"
        sql = sql % ('%s', wheres, '%s', '%s')
        count_sql = count_sql % ('%s', wheres)

        ret = yield self.executejson(sql, (school_id, page, offset))
        count = yield self.execute(count_sql, (school_id))

        raise gen.Return((ret, count[0][0]))

    @gen.coroutine
    def find_device(self, Fid):
        sql = '''SELECT Fid, Fname, Ftype_id, Fdorm_id, Froom_id  
                , Ffloor, Fimg, Fqrcode, Fqrcode, Fcreate_time, Fcode
                FROM tb_material 
                WHERE Fid = %s '''

        ret = yield self.executejson(sql, (Fid))

        if len(ret) >= 1:
            raise gen.Return(ret[0])
        else:
            raise gen.Return({})

    @gen.coroutine
    def del_device(self, Fid):
        sql = 'DELETE FROM tb_material WHERE Fid = %s'

        ret = yield self.upsert_commit(sql, (Fid))

        raise gen.Return(ret)

    @gen.coroutine
    def update_device(self, Fid, arg):
        device_sql = '''UPDATE tb_material SET %s WHERE Fid = %s'''

        update_data = ''
        for i in arg.keys():
            update_data += i + '=%s,'

        device_sql = device_sql % (update_data[0:-1], '%s')

        data = arg.values()
        data.append(Fid)

        ret = yield self.upsert(device_sql, tuple(data))

        self.commit()

        raise gen.Return(ret)
